<?php
/**
  * Musashi - A project manager for music groups and bands
  * Copyright (C) 2010 Daniel Torres
  *
  * This program is free software: you can redistribute it and/or modify
  * it under the terms of the GNU General Public License as published by
  * the Free Software Foundation, either version 3 of the License, or
  * (at your option) any later version.
  *
  * This program is distributed in the hope that it will be useful,
  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  * GNU General Public License for more details.
  *
  * You should have received a copy of the GNU General Public License
  * along with this program.  If not, see <http://www.gnu.org/licenses/>.
  *
  */

/**
 *  Controls Musashi's show operations
 *  
 * @package com_musashi
 * @author Daniel Torres
 */
 
// Make sure we have access to this page
defined('_JEXEC') or die ('Restricted access');

jimport('joomla.application.component.model');

/**
 *  
 *  Model for file related operations
 *  @author Daniel Torres
 */
 class MusashiModelShows extends JModel
 {
    // The Musashi ID of the current member (as specified on the Member table)
    var $_musashiMemberID = null;       
    
    /**
     *   Array of show status identifiers
     *   Show status is represented as a numeric identifier. Most shows would make
     *   use of the following, but if you need to add/remove, remember that past
     *   shows keep their numeric status, and are not modified unless you update them
     *   manually.
     */
    var $_statusLabels = array (
      0 => "Unconfirmed",           // We still don't know if we are doing this show
      1 => "Cancelled",             // This show has been canceled
      2 => "Confirmed",             // This show has been confirmed: We are doing it
    );
    
    /**
     *   Array of show notification targets
     *   Represented on HTML by their numeric identifier. These define the kind
     *   of people who should receive mail for different show information updates.
     */
    var $_notificationTargets = array (
      0 => "Everyone in the group", // Absolutely everyone
      1 => "Everyone in this show", // Just subscribers, wether confirmed, or not.
      2 => "Confirmed members",     // People who will participate for sure
      3 => "Non-confirmed members", // The unsures
      4 => "Nobody",                // Nobody at all
    );
    
    /**
     *  Array of user status identifiers
     *  Shows the participation status that each user can incur. Note that
     *  'non-participating' status are simply denoted by an absence of an entry
     *  into the Member_Show table. In other words, "not participating" is not
     *  a necessary status
     */
    var $_userStatusLabels = array (
        0 => "confirmed",           // The user confirms his/her participation
        1 => "on call",             // The user participates if necessary only
        2 => "tentative",           // Maybe. Not decided yet.    
    );
     
    
   /**
     *  Obtains the member id of the current user. This function could be placed
     *  somewhere else (like the user module) and just passed to all other models,
     *  but for the sake of simplicity (and perhaps bad design) I'm just repeating it.
     *  
     *  WARNING: It is assumed that we have a member entry!
     */
    function getMemberID()
    {
        if( $this->_musashiMemberID == null )
        {
            // Get the current user
            $user =& JFactory::getUser();
        
            // See if we have him/her
            $query = "SELECT id FROM #__Musashi_Member WHERE userId =".$user->id;
            $this->_db->setQuery($query);
            $result = $this->_db->loadObject();
            
            if( $result != null ){
                $this->_musashiMemberID = $result->id;
            }

        }
        return $this->_musashiMemberID;
    }
    
    /**
     *  Creates a combo box with the labels of the provided object
     *  @param a_labelArray     An array with text labels
     *  @param a_defaultIndex   Index for the defauld selection 
     *  @return A properly formed HTML option tag set
     */
    function createHTMLCombo( &$a_labelArray, $a_defaultIndex = null )
    {
      if ($a_defaultIndex == null){
         $a_defaultIndex = 0;
      }
      
      // Create combo
      $htmlCode = "";
      foreach( $a_labelArray as $key => $value )
      {
         if($key == $a_defaultIndex){
            $htmlCode .= sprintf("<option value=\"%d\" SELECTED >%s</option> ", $key, $value );                    
         } else {
            $htmlCode .= sprintf("<option value=\"%d\">%s</option> ", $key, $value );                    
         }
      }
      
      return $htmlCode;        
    }
    
    /**
     *   Creates a properly formatted combo to select available status for a show.
     *   If no status is provided, defaults to zero
     *   @param a_statusIndex
     */
    function getStatusHTMLCombo( $a_statusIndex = null )
    {
        return $this->createHTMLCombo($this->_statusLabels, $a_statusIndex );
    }
    
    /**
     *  Creates a properly formatted combo to select an user's participation status
     *  @param a_statusIndex Default status index
     */
    function getUserStatusHTMLCombo( $a_statusIndex = null )
    {
        return $this->createHTMLCombo($this->_userStatusLabels, $a_statusIndex );
    }
    
    /**
     *   Creates a properly formatted combo to select notification targets for an action
     */
    function getNotificationTargetsHTMLCombo()
    {
        return $this->createHTMLCombo($this->_notificationTargets);
    }
    
    /**
     *   Creates a show by adding its bare minimum information amount.
     *   The current user will be registered as the creator.
     *   @param a_name        Show name
     *   @param a_dateTime    Show date and time. Format: YYYY-MM-DD HH:mm:SS
     *   @return The ID of the newly created show, or false if the operation failed
     */
    function addShow( $a_name, $a_dateTime )
    {
      $sql =  sprintf("INSERT INTO #__Musashi_Show (creator,name,datetime,status) VALUES (%d,'%s','%s',0)",
                      $this->getMemberID(),$a_name,$a_dateTime);
      
      $this->_db->setQuery( $sql );
      if( $this->_db->query() )
      {
         return mysql_insert_id();            
      }
      else
      {
         return false;
      }
    }
    
    /**
     *   Updates the basic information of a show
     *   @param a_sID      Show's id
     *   @param a_dateTime Show date and time. Same format as with addShow
     *   @param a_contact  Show's main point of contact
     *   @param a_notes    Show's notes
     *   @param a_status   Show's numerical status index
     *   @param a_pay      Show's pay
     *   @return True if the show is updated sucessfully
     */
    function updateShowBasic( $a_sID, $a_dateTime, $a_contact, $a_notes, $a_status, $a_pay )
    {
      $sql = sprintf(
         "UPDATE #__Musashi_Show ".
         "SET datetime='%s', contact='%s', notes='%s', status=%d, pay=%f ".
         "WHERE id=%d",
         $a_dateTime, $a_contact, $a_notes, $a_status, $a_pay, $a_sID );
      
      $this->_db->setQuery( $sql );
      return $this->_db->query();      
    }
    
    /**
     *  Tell if the current user has a given role in the provided show ID
     *  @param a_showID The show we are interested in
     *  @param a_roleID ID of the role we are testing this user for
     */
    function userHasRole( $a_showID, $a_roleID )
    {
        $sql =  "SELECT idMember FROM ".
                "#__Musashi_Member_Show ".
                "WHERE idShow = ".$a_showID." AND idRole = ".$a_roleID;
                
        $this->_db->setQuery( $query );
        $idMember = $this->_db->loadResult();
        if ( $idMember && ($idMember == $this->getMemberID()) )
        {
            return true;
        }
        else
        {
            return false;
        }


    }
    
    /**
     *  Quickly tell if the current user is the creator of the provided show ID
     */
    function isShowCreator( $a_showID )
    {
        $sql = "SELECT creator FROM #__Musashi_Show WHERE id = ".$a_showID;
        $this->_db->setQuery( $sql );
        
        $idCreator = $this->_db->loadResult();
        if( ($idCreator != null ) && ($idCreator == $this->getMemberID() ) )
        {
            return true;           
        }
        
        return false;
        
    }
    
    /**
     *  Returns information about this show, or False if the show doesn't exist
     *  @param a_showID The id of the queried show
     *  @return An array of entries in the form [id,venue,creator,name,date,contact,floorplan,notes,status,pay]
     */
    function getShowData( $a_showID )
    {
        $sql = "SELECT * FROM #__Musashi_Show WHERE id = ".$a_showID;
        $this->_db->setQuery($sql);
        return $this->_db->loadAssoc();
    }
    
    /**
     *  Returns all known roles
     *  @return A named array in the form [id,name]
     */
    function getShowRoles()
    {
        $this->_db->setQuery('SELECT id, name FROM #__Musashi_Role');
        return $this->_db->loadAssocList();
    }
    
    /**
     *   Returns information about the queried venue
     *   @param a_venueID ID for this venue
     *   @return An array of entries in the form [id,name,description,address,contact,notes,link]
     */
    function getVenueData( $a_venueID )
    {
        $sql = "SELECT * FROM #__Musashi_Venue WHERE id = ".$a_venueID;
        $this->_db->setQuery($sql);
        return $this->_db->loadAssoc();         
    }
    
    /**
     *  Returns the name of the creator of this show
     *  @param a_showID The id of the queried show
     *  @return The username of the show's creator
     */
    function getShowCreator( $a_showID )
    {
        $sql = "SELECT u.name FROM ".
               "#__users AS u, #__Musashi_Member AS m, #__Musashi_Show AS s ".
               "WHERE ".
                    "u.id = m.userId ".
                    "AND s.creator = m.id ".
                    "AND s.id = ".$a_showID;
        
        $this->_db->setQuery($sql);
        return $this->_db->loadResult();
    }
    
    /**
     *   Returns a list of available venues, in the form of [id,name]
     */
    function getVenueList()
    {
      $sql = "SELECT * FROM #__Musashi_Venue";
      $this->_db->setQuery($sql);
      return $this->_db->loadAssocList();
    }
    
    /**
     *   Adds a new venue to our db
     *   Params: All the fields of the venue datatable
     *   @return  A new venue ID if successful. False otherwise.
     */
    function addVenue( $a_Name, $a_Description, $a_Address, $a_Contact, $a_Notes, $a_Link )
    {
      $sql =  sprintf("INSERT INTO #__Musashi_Venue (name,description,address,contact,notes,link) VALUES ('%s','%s','%s','%s','%s','%s')",
                      $a_Name, $a_Description, $a_Address, $a_Contact, $a_Notes, $a_Link);
      
      $this->_db->setQuery( $sql );
      if( $this->_db->query() )
      {
         return mysql_insert_id();            
      }
      else
      {
         return false;
      }      
    }
    
    /**
     *   Links a show to the provided venue ID
     *   @param a_showID   Id of the show to modify
     *   @param a_venueID  Id of the venue to include
     */
    function setShowVenue( $a_showID, $a_venueID )
    {
      $sql = sprintf("UPDATE #__Musashi_Show SET venue=%d WHERE id=%d", $a_venueID, $a_showID );
      $this->_db->setQuery( $sql );
      return $this->_db->query();            
    }
    
    /**
     *   Updates the comments (notes) of a given venue
     *   @param a_venueID  ID of the venue to modify
     *   @param a_notes    Notes to update on this venue
     */
    function updateVenueNotes( $a_venueID, $a_notes )
    {
      $sql = sprintf("UPDATE #__Musashi_Venue SET notes='%s' WHERE id=%d", $a_notes, $a_venueID);
      $this->_db->setQuery( $sql );
      return $this->_db->query();                  
    }
    
    /**
     *  Returns the list of distinct members registered on a given show
     *  @return An named array of the form [id,name]
     */
    function getKnownMembers( $a_showID )
    {
        /**
         *  This was great, but not what we needed
         * $sql = 'SELECT ms.idMember, u.name, ms.idRole, ms.status '.
         *      'FROM #__Musashi_Member_Show as ms '.
         *     'INNER JOIN #__Musashi_Member as m ON ms.idMember = m.id '.
         *     'INNER JOIN #__Musashi_Show as s   ON ms.idShow = s.id '.
         *     'INNER JOIN #__users as u          ON m.userId = u.id '.
         *     'WHERE s.id = '.$a_showID.' ORDER BY ms.status ';
         */
        $sql = 'SELECT DISTINCT m.id, u.name FROM '.
               '#__Musashi_Member as m '.
               'INNER JOIN #__users as u                ON m.userId = u.id '.               
               'INNER JOIN #__Musashi_Member_Show as ms ON ms.idMember = m.id '.
               'WHERE ms.idShow = '.$a_showID;
        

        $this->_db->setQuery($sql);
        $results = $this->_db->loadAssocList();

        return $results;        
    }
    
    /**
     *  Returns an array of roles for a provided member ID, under the provided
     *  show
     *  @param a_memberID ID of the queried member
     *  @param a_showID   ID for the requested show
     *  @return A named array of the form ['idRole', 'status']
     */
    function getMemberRolesForShow( $a_memberID, $a_showID )
    {
        $sql = 'SELECT idRole, status FROM #__Musashi_Member_Show '.
               'WHERE idMember = '.$a_memberID.' AND idShow = '.$a_showID;
               
        $this->_db->setQuery($sql);
        $results = $this->_db->loadAssocList();

        return $results;                
    }
    /**
     *  Returns the list of members that haven't responded to a given show
     *  @param $a_showID ID of the queried show
     *  @return A named array of the form [id,name]
     */
    function getUnknownMembers( $a_showID )
    {
        $sql = 'SELECT m.id, u.name FROM '.
               '#__Musashi_Member as m INNER JOIN #__users as u '.
               'ON m.userId = u.id '.
               'WHERE m.id NOT IN ('.
                    'SELECT idMember FROM '.
                    '#__Musashi_Member_Show WHERE '.
                    'idShow = '.$a_showID.')';

        $this->_db->setQuery($sql);
        $results = $this->_db->loadAssocList();

        return $results;                
        
    }
    
    /**
     *   Registers/updates an user for a given show, under the provided role.
     *   Note that the same user can be registered with more than one role on
     *   the same show
     *   @param a_showID ID of the show
     *   @param a_userID ID of the user
     *   @param a_roleID ID of the role
     *   @param a_statID ID of the status
     */
    function setMemberRole( $a_showID, $a_userID, $a_roleID, $a_statID )
    {
      $sql =  sprintf("REPLACE INTO #__Musashi_Member_Show (idMember,idShow,idRole,status) VALUES (%d,%d,%d,%d)",
                      $a_userID, $a_showID, $a_roleID, $a_statID);
      
      $this->_db->setQuery( $sql );
      return $this->_db->query();
    }
    

    
 }
?>
